﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data.SQLite;
using System.Globalization;
using PetroFexService.FuelSupplyServiceReference;
using PetroFexService.ReportServiceReference;

namespace NSPetroFexService
{
    public class PumpEventArgs : EventArgs
    {
        public PumpEventArgs(string pumpId, float litresPumped, float thisSale)
        {
            this.PumpId = pumpId;
            this.LitresPumped = litresPumped;
            this.ThisSale = thisSale;
        }
        private string pumpId;
        public string PumpId
        {
            get { return pumpId; }
            set { pumpId = value; }
        }
        public float litresPumped;
        public float LitresPumped
        {
            get { return litresPumped; }
            set { litresPumped = value; }
        }
        public float thisSale;
        public float ThisSale
        {
            get { return thisSale; }
            set { thisSale = value; }
        }
    }

    public class POSEventArgs : EventArgs
    {
        public POSEventArgs(string posId)
        {
            this.PosId = posId;
        }
        private string posId;
        public string PosId
        {
            get { return posId; }
            set { posId = value; }
        }
    }
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in both code and config file together.
    [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single, ConcurrencyMode=ConcurrencyMode.Multiple)]
    public class PetroFexService : IPetroFexService
    {
        public static event EventHandler<PumpEventArgs> pumpConnected;
        public static event EventHandler<PumpEventArgs> pumpDisconnected;
        public static event EventHandler<PumpEventArgs> pumpUpdated;
        public static event EventHandler<POSEventArgs> POSTerminalConnected;
        public static event EventHandler<POSEventArgs> POSTerminalDisconnected;
        public static event EventHandler ManagerSystemConnected;
        public static event EventHandler ManagerSystemDisconnected;

        private SQLiteConnection sqlConnection = new SQLiteConnection("Data source=petroFexDatabase.db; Version=3;");
        private List<PetrolPump> m_PetrolPumps = new List<PetrolPump>();
        private List<string> m_POSIds = new List<string>();
        private FuelSupplyServiceClient fuelSupplyClient = new FuelSupplyServiceClient();
        private ReportServiceClient reportServiceClient = new ReportServiceClient();
        public bool ConnectPOS(string POSId)
        {
             m_POSIds.Add(POSId);
            if (POSTerminalConnected != null)
                POSTerminalConnected(null, new POSEventArgs(POSId));
            OpenSQLiteConnection(); //if POS Connected, open SQL connection so transactions can be saved.
            return true;
        }
        public bool DisconnectPOS(string POSId)
        {
            m_POSIds.RemoveAll(pos => pos == POSId);
            m_POSIds.TrimExcess();
            if (POSTerminalDisconnected != null)
                POSTerminalDisconnected(null, new POSEventArgs(POSId));
            return true;
        }

        public bool ConnectPump(string pumpId)
        {
            m_PetrolPumps.Add(new PetrolPump(pumpId));
            if (pumpConnected != null)
                pumpConnected(null, new PumpEventArgs(pumpId, 0.0f, 0.0f));
            OpenSQLiteConnection();
            return true;
        }
        public bool DisconnectPump(string pumpId)
        {
            m_PetrolPumps.RemoveAll(pump => pump.PumpId == pumpId);
            m_PetrolPumps.TrimExcess();
            if (pumpDisconnected != null)
                pumpDisconnected(null, new PumpEventArgs(pumpId, 0.0f, 0.0f));
            return true;
        }
        public bool UpdatePump(string pumpId, float litresPumped, float thisSale)
        {
            foreach (PetrolPump pp in m_PetrolPumps)
            {
                if (pumpId == pp.PumpId)
                {
                    pp.LitresPumped = litresPumped;
                    pp.ThisSale = thisSale;
                }
            }
            if (pumpUpdated != null)
                pumpUpdated(null, new PumpEventArgs(pumpId, litresPumped, thisSale));
            return true;
        }
        public List<PetrolPump> getPumps()
        {
            return m_PetrolPumps;
        }
        public PetrolPump getPump(string pumpId)
        {
            return m_PetrolPumps.First(p => p.PumpId == pumpId);
        }
        public void ActivatePump(string pumpId)
        {
            foreach (PetrolPump pp in m_PetrolPumps)
            {
                if (pp.PumpId == pumpId)
                    pp.PumpActive = true;
            }
        }
        public void DeactivatePump(string pumpId)
        {
            foreach (PetrolPump pp in m_PetrolPumps)
            {
                if (pp.PumpId == pumpId)
                    pp.PumpActive = false;
            }
        }

        public bool MakeCustomerReady(string pumpId, string fuel)
        {
            foreach (PetrolPump pp in m_PetrolPumps)
            {
                if (pp.PumpId == pumpId)
                {
                    pp.CustomerReady = true;
                    pp.CurrentFuelType = fuel;
                }
            }
            return true;
        }
        public bool MakeCustomerUnready(string pumpId)
        {
            foreach (PetrolPump pp in m_PetrolPumps)
            {
                if (pp.PumpId == pumpId)
                {
                    pp.CustomerReady = false;
                    pp.CurrentFuelType = "None";
                }
            }
            return true;
        }

        public void LogCustomerPetrolPurchase(string fuel, float amount, float price)
        {
            SQLiteCommand insertLog = sqlConnection.CreateCommand();
            insertLog.CommandText =
                "INSERT INTO CustomerFuelPurchases (Time, Amount, Price, Fuel) VALUES (@Time, @Amount, @Price, @Fuel)";
            insertLog.Parameters.Add(new SQLiteParameter("@Time"));
            insertLog.Parameters.Add(new SQLiteParameter("@Amount"));
            insertLog.Parameters.Add(new SQLiteParameter("@Price"));
            insertLog.Parameters.Add(new SQLiteParameter("@Fuel"));

            //Set param values
            insertLog.Parameters["@Time"].Value = DateTime.Now.Date.ToShortDateString();
            insertLog.Parameters["@Amount"].Value = amount.ToString("N", new CultureInfo("en-GB"));
            insertLog.Parameters["@Price"].Value = price.ToString("N", new CultureInfo("en-GB"));
            insertLog.Parameters["@Fuel"].Value = fuel;
            insertLog.ExecuteNonQuery();

            List<float> currentLevels = GetPetrolTankLevels();
            SQLiteCommand updateTankLevel = sqlConnection.CreateCommand();
            if (fuel == "Unleaded")
            {
                string level = (currentLevels[0] - amount).ToString();
                updateTankLevel.CommandText = "UPDATE FuelTanks SET Amount=" + level + " WHERE Fuel='Unleaded'";
            }
            else if (fuel == "Diesel")
            {
                string level = (currentLevels[1] - amount).ToString();
                updateTankLevel.CommandText = "UPDATE FuelTanks SET Amount=" + level + " WHERE Fuel='Diesel'";
            }
            updateTankLevel.ExecuteNonQuery();
        }
        public List<float> GetPetrolTankLevels()
        {
            float unleadedLevel = 0.0f;
            float dieselLevel = 0.0f;
            SQLiteDataReader unleadedReader, dieselReader;

            SQLiteCommand getUnleadedLevel = sqlConnection.CreateCommand();
            getUnleadedLevel.CommandText = "SELECT Amount FROM FuelTanks WHERE Fuel='Unleaded'";
            SQLiteCommand getDieselLevel = sqlConnection.CreateCommand();
            getDieselLevel.CommandText = "SELECT Amount FROM FuelTanks WHERE Fuel='Diesel'";

            unleadedReader = getUnleadedLevel.ExecuteReader();
            dieselReader = getDieselLevel.ExecuteReader();

            while (unleadedReader.Read())
            {
                unleadedLevel = float.Parse(unleadedReader["Amount"] + "");
            }
            while (dieselReader.Read())
            {
                dieselLevel = float.Parse(dieselReader["Amount"] + "");
            }
            return new List<float> { unleadedLevel, dieselLevel };
        }

        private void OpenSQLiteConnection()
        {
            if (sqlConnection.State != System.Data.ConnectionState.Open) //if connection is not already open
            {
                sqlConnection.Open(); //open that connection
            }
            SQLiteCommand createTablesCommand = sqlConnection.CreateCommand();
            createTablesCommand.CommandText = "CREATE TABLE IF NOT EXISTS CustomerFuelPurchases(Time varchar(25), Amount varchar(10), Price varchar(10), Fuel varchar(10), WholesalePrice varchar(10))";
            createTablesCommand.ExecuteNonQuery();
            createTablesCommand.CommandText = "CREATE TABLE IF NOT EXISTS FuelTanks(Fuel varchar(10), Amount varchar(10))";
            createTablesCommand.ExecuteNonQuery();
            createTablesCommand.CommandText = "CREATE TABLE IF NOT EXISTS BulkFuelPurchases(Date varchar(25), Amount varchar(10), Price varchar(10), Fuel varchar(10))";
            createTablesCommand.ExecuteNonQuery();
            createTablesCommand.CommandText = "CREATE TABLE IF NOT EXISTS FuelPrices(UnleadedPrice varchar(10), DieselPrice varchar(10))";
            createTablesCommand.ExecuteNonQuery();

            //Commands to programmatically create full fuel tanks, if for some inexplicable reason the .db file was deleted.
            SQLiteCommand sqlCommander = sqlConnection.CreateCommand();
            sqlCommander.CommandText =
                "SELECT COUNT (*) FROM FuelTanks WHERE Fuel='Unleaded'";
            int result = Convert.ToInt32(sqlCommander.ExecuteScalar());
            if (result == 0) //if no result found
            {
                sqlCommander.CommandText = "INSERT INTO FuelTanks (Fuel, Amount) VALUES (@Fuel, @Amount)";
                sqlCommander.Parameters.Add(new SQLiteParameter("@Fuel"));
                sqlCommander.Parameters.Add(new SQLiteParameter("@Amount"));
                sqlCommander.Parameters["@Fuel"].Value = "Unleaded";
                sqlCommander.Parameters["@Amount"].Value = "10000.0";
                sqlCommander.ExecuteNonQuery();
            }
            sqlCommander.Parameters.Clear();
            sqlCommander.CommandText =
                "SELECT COUNT (*) FROM FuelTanks WHERE Fuel='Diesel'";
            result = Convert.ToInt32(sqlCommander.ExecuteScalar());
            if (result == 0) //if no result found
            {
                sqlCommander.CommandText = "INSERT INTO FuelTanks (Fuel, Amount) VALUES (@Fuel, @Amount)";
                sqlCommander.Parameters.Add(new SQLiteParameter("@Fuel"));
                sqlCommander.Parameters.Add(new SQLiteParameter("@Amount"));
                sqlCommander.Parameters["@Fuel"].Value = "Diesel";
                sqlCommander.Parameters["@Amount"].Value = "10000.0";
                sqlCommander.ExecuteNonQuery();
            }

            //Create fuel prices
            sqlCommander.Parameters.Clear();
            sqlCommander.CommandText =
                "SELECT COUNT (*) FROM FuelPrices";
            result = Convert.ToInt32(sqlCommander.ExecuteScalar());
            if (result == 0) //if no result found
            {
                sqlCommander.CommandText = "INSERT INTO FuelPrices (UnleadedPrice, DieselPrice) VALUES (@UnleadedPrice, @DieselPrice)";
                sqlCommander.Parameters.Add(new SQLiteParameter("@UnleadedPrice"));
                sqlCommander.Parameters.Add(new SQLiteParameter("@DieselPrice"));
                sqlCommander.Parameters["@UnleadedPrice"].Value = "1.00";
                sqlCommander.Parameters["@DieselPrice"].Value = "1.00";
                sqlCommander.ExecuteNonQuery();
            }
        }

        public bool ConnectManagerSystem()
        {
            if (ManagerSystemConnected != null)
                ManagerSystemConnected(null, null);
            OpenSQLiteConnection();
            return true;
        }
        public bool DisconnectManagerSystem()
        {
            if (ManagerSystemDisconnected != null)
                ManagerSystemDisconnected(null, null);
            return true;
        }

        public List<float> GetFuelPrices()
        {
            float unleadedPrice = 0.0f;
            float dieselPrice = 0.0f;
            SQLiteDataReader reader;

            SQLiteCommand getPrices = sqlConnection.CreateCommand();
            getPrices.CommandText = "SELECT * FROM FuelPrices";

            reader = getPrices.ExecuteReader();

            while (reader.Read())
            {
                unleadedPrice = float.Parse(reader["UnleadedPrice"] + "");
                dieselPrice = float.Parse(reader["DieselPrice"] + "");
            }
            return new List<float> { unleadedPrice, dieselPrice };
        }
        public void SetFuelPrices(float unleadedPrice, float dieselPrice)
        {
            SQLiteCommand setPrices = sqlConnection.CreateCommand();
            string unleadedPriceStr = unleadedPrice.ToString("N", new CultureInfo("en-GB"));
            string dieselPriceStr = dieselPrice.ToString("N", new CultureInfo("en-GB"));
            setPrices.CommandText = "DELETE FROM FuelPrices"; //delete record
            setPrices.ExecuteNonQuery();
            setPrices.CommandText = "INSERT INTO FuelPrices (UnleadedPrice, DieselPrice) VALUES (@UnleadedPrice, @DieselPrice)";
            setPrices.Parameters.Add(new SQLiteParameter("@UnleadedPrice"));
            setPrices.Parameters.Add(new SQLiteParameter("@DieselPrice"));
            setPrices.Parameters["@UnleadedPrice"].Value = unleadedPriceStr;
            setPrices.Parameters["@DieselPrice"].Value = dieselPriceStr;
            setPrices.ExecuteNonQuery();
        }

        public List<float> GetFuelSales(string date, string fuel)
        {
            List<float> sales = new List<float>();
            SQLiteCommand getSalesCommand = sqlConnection.CreateCommand();
            getSalesCommand.CommandText = "SELECT * FROM CustomerFuelPurchases WHERE Time='" + date + "' AND Fuel='" + fuel +"'";
            SQLiteDataReader reader = getSalesCommand.ExecuteReader();
            while (reader.Read())
            {
                sales.Add(float.Parse(reader["Amount"] + ""));
                sales.Add(float.Parse(reader["Price"] + ""));
            }
            return sales;
        }
    }
}
